import io
import os
import time


def Error_sql():
    # 项目路径
    BASE_DIR = os.path.dirname(os.path.abspath('base_dir'))
    # 静态文件夹的路径,项目路径拼接向下的文件夹
    STATIC_DIR = os.path.join(BASE_DIR, 'static')
    sqls_path = os.path.join(STATIC_DIR, 'sqls')

    # 找到txt文件
    ct_txt = sqls_path + "\\create_sql.txt"
    ins_txt = sqls_path + "\\insert_sql.txt"

    # 时间戳
    times = str(int(time.mktime(time.localtime())))
    # 错误信息变量
    error_msg = ''

    with open(ct_txt, "r", encoding='utf-8') as ct:

        for ct_line in ct.readlines():
            if ct_line.find("partitioned BY"):
                ct_ref_num = 0
                ins_ref_num = 0
                ref_pos1 = ct_line.find("d BY (")
                ref_pos2 = ct_line.find(");")
                table_pos1 = ct_line.find("EXISTS")
                table_pos2 = ct_line.find(" (")
                ct_ref_num = ct_line[ref_pos1 + 1:ref_pos2].count('COMMENT')
                print(ct_line[table_pos1 + 7:table_pos2] + ':分区字段数量:' + str(ct_ref_num))
                ct_fqs = ''
                for ct_fq in ct_line[ref_pos1 + 6:ref_pos2].split(','):
                    ct_table_name = ct_line[table_pos1 + 7:table_pos2]
                    pos = ct_fq.find(" string")
                    ct_fqs += ct_fq[0:pos]
                for ct_fq in ct_line[ref_pos1 + 6:ref_pos2].split(','):
                    ct_table_name = ct_line[table_pos1 + 7:table_pos2]
                    pos = ct_fq.find(" string")
                    # 分区字段
                    with open(ins_txt, "r", encoding='utf-8') as ins:
                        for ins_line in ins.readlines():
                            ins_pos1 = ins_line.find("(")
                            ins_pos2 = ins_line.find(")")
                            table_pos3 = ins_line.find(".")
                            table_pos4 = ins_line.find(" PARTITI")
                            ins_table_name = ins_line[table_pos3 + 1:table_pos4]
                            # 两个文件中表格匹配
                            if ct_table_name == ins_table_name:
                                ins_fqs = ''
                                ins_ref_num = ins_line[ins_pos1 + 1:ins_pos2].count('=')
                                if ct_ref_num > ins_ref_num:
                                    # 插入数据文件
                                    error_file = io.open(sqls_path + "\\error_sql_" + times + ".txt", 'a',
                                                         encoding='utf-8')
                                    # 逗号分割语句存入list中(rfq='20210831',sjlybz='GSZJ')
                                    for ins_fq in ins_line[ins_pos1 + 1:ins_pos2].split(','):
                                        pos_fq = ins_fq.find("=")
                                        ins_fqs += ins_fq[0:pos_fq] + ','
                                    exist_flag = ins_fqs.find(ct_fq[0:pos])
                                    # 找到返回0，未找到返回-1
                                    if exist_flag != -1:
                                        pass
                                    else:
                                        error_msg += ct_table_name + '中的' + ct_fq[0:pos] + '在insert文件中不存在'
                                        error_file.write(ct_table_name + '中的' + ct_fq[0:pos] + "在insert文件中不存在不存在\n")
                                elif ct_ref_num < ins_ref_num:
                                    # 插入数据文件
                                    error_file = io.open(sqls_path + "\\error_sql_" + times + ".txt", 'a',
                                                         encoding='utf-8')
                                    # 逗号分割语句存入list中(rfq='20210831',sjlybz='GSZJ')
                                    for ins_fq in ins_line[ins_pos1 + 1:ins_pos2].split(','):
                                        pos_fq = ins_fq.find("=")
                                        exist_flag = ct_fqs.find(ins_fq[0:pos_fq])
                                    # 找到返回0，未找到返回-1
                                    if exist_flag != -1:
                                        pass
                                    else:
                                        error_msg += ct_table_name + '中的' + ins_fq[0:pos_fq] + '在create文件中不存在'
                                        error_file.write(ct_table_name + '中的' + ins_fq[0:pos_fq] + "在create文件中不存在不存在\n")

    ct.close()
    ins.close()
    if len(error_msg) > 0:
        return "error_sql_" + times + ".txt"
    else:
        return "no_error"


if __name__ == '__main__':
    print(Error_sql())
